import pymongo
from config.configs import ALPHA_DB_HOST, ALPHA_DB_PORT
from datetime import datetime
from tools.funciton import spuId_to_articleNumber, skuId_to_size, write_xlsx

alpha = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).alpha
jw = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).jiwu
taowu = pymongo.MongoClient(ALPHA_DB_HOST, ALPHA_DB_PORT).taowu


def speed_stocks_export(db):
    """极速仓库存导出"""
    datas = []
    pipeline = [
        {
            "$match": {'warehouseType': "common", 'qty': {'$ne': 0}, 'uniqueCode': {"$ne": None}}
        },
        {
            "$lookup": {
                "from": "warehouses",
                "localField": "warehouseCode",
                "foreignField": "code",
                "as": "result"
            }
        },
        {
            "$lookup": {
                "from": "merchants",
                "localField": "merchantCode",
                "foreignField": "code",
                "as": "merchant"
            }
        },
        {
            "$unwind": {
                "path": "$result"
            }
        },
        {
            "$unwind": {
                "path": "$merchant"
            }
        },
        {
            "$project": {
                "_id": 0,
                "appointNo": 1,
                "merchantCode": 1,
                "merchantName": "$merchant.name",
                "spuId": 1,
                "skuId": 1,
                "failMemo": 1,
                "uniqueCode": 1,
                "warehouse": "$result.name",
                "shelfCode": 1,
                "qty": 1,
                "costUnitPrice": 1
            }
        },
        {
            "$lookup": {
                "from": "spus",
                "localField": "spuId",
                "foreignField": "spuId",
                "as": "spu"
            }
        },
        {
            "$lookup": {
                "from": "skus",
                "localField": "skuId",
                "foreignField": "skuId",
                "as": "sku"
            }
        },
        {
            "$addFields": {
                "spu": {
                    "$arrayElemAt": ["$spu", 0]
                },
                "sku": {
                    "$arrayElemAt": ["$sku", 0]
                }
            }
        },
        {
            "$addFields": {
                "articleNumber": "$spu.articleNumber",
                "status": {
                    "$switch": {
                        "branches": [
                            {
                                "case": {
                                    "$regexMatch": {
                                        "input": "$uniqueCode",
                                        "regex": "S"
                                    }
                                },
                                "then": "全新"
                            },
                            {
                                "case": {
                                    "$regexMatch": {
                                        "input": "$uniqueCode",
                                        "regex": "E"
                                    }
                                },
                                "then": "直退瑕疵"
                            }
                        ],
                        "default": "$failMemo"
                    }
                },
                "size": {
                    "$function": {
                        "body": "function (jsonString) { return Object.values(JSON.parse(jsonString)).join(' '); }",
                        "args": ["$sku.properties"],
                        "lang": "js"
                    }
                }
            }
        },
        {
            "$project": {
                "_id": 0,
                "预约单号": "$appointNo",
                "商家编号": "$merchantCode",
                "商家名称": "$merchantName",
                "货号": "$articleNumber",
                "尺码": "$size",
                "瑕疵原因": "$status",
                "唯一码": "$uniqueCode",
                "所在仓库": "$warehouse",
                "货架号": "$shelfCode",
                "库存数量": "$qty",
                "成本单价": {
                    "$divide": ["$costUnitPrice", 100]
                }
            }
        }
    ]

    if db == '集物':
        res = jw.stocks.aggregate(pipeline=pipeline)
        for i in res:
            datas.append(
                list(i.values()))
    elif db == '淘物':
        res = taowu.stocks.aggregate(pipeline=pipeline)
        for i in res:
            datas.append(
                list(i.values()))
    write_xlsx(
        ['入仓预约单', '商家编号', '商家名称', '货号', '尺码', '瑕疵原因', '唯一码', '所在仓库', '货架号', '库存数量',
         '成本单价'],
        datas, f'{db}极速仓{datetime.now().strftime("%m%d")}')


speed_stocks_export('集物')


def flash_stock_export(db):
    """闪电仓库存导出"""
    li = []
    pipeline = [
        {
            "$match": {
                "qty": {
                    "$ne": 0
                },
                "warehouseType": "dewuflash"
            }
        },
        {
            "$addFields": {
                "stockid": {
                    "$toString": "$_id"
                }
            }
        },
        {
            "$lookup": {
                "from": "orders",
                "localField": "stockid",
                "foreignField": "stockId",
                "as": "result"
            }
        },
        {
            "$unwind": {
                "path": "$result",
                "preserveNullAndEmptyArrays": True
            }
        },
        {
            "$addFields": {
                "orders": {
                    "$switch": {
                        "branches": [
                            {
                                "case": {
                                    "$in": [
                                        "$result.orderStatus",
                                        ["3030", "3040", "4000"]
                                    ]
                                },
                                "then": "$result.qty"
                            },
                            {
                                "case": {
                                    "$and": [
                                        {
                                            "$eq": [
                                                "$result.orderStatus",
                                                "8010"
                                            ]
                                        },
                                        {
                                            "$eq": [
                                                "$result.closeType",
                                                "20"
                                            ]
                                        }
                                    ]
                                },
                                "then": "$result.qty"
                            }
                        ],
                        "default": 0
                    }
                }
            }
        },
        {
            "$group": {
                "_id": "$stockid",
                "sold": {
                    "$sum": "$orders"
                },
                "stock": {
                    "$first": "$qty"
                },
                "spuId": {
                    "$first": "$spuId"
                },
                "skuId": {
                    "$first": "$skuId"
                },
                "merchantCode": {
                    "$first": "$merchantCode"
                },
                "merchantName": {
                    "$first": "$merchantName"
                },
                "warehouseName": {
                    "$first": "$warehouseName"
                },
                "appointNo": {
                    "$first": "$appointNo"
                }
            }
        },
        {
            "$addFields": {
                "stock_num": {
                    "$subtract": ["$stock", "$sold"]
                }
            }
        },
        {
            "$match": {
                "stock_num": {
                    "$ne": 0
                }
            }
        },
        {
            "$lookup": {
                "from": "spus",
                "localField": "spuId",
                "foreignField": "spuId",
                "as": "spu"
            }
        },
        {
            "$lookup": {
                "from": "skus",
                "localField": "skuId",
                "foreignField": "skuId",
                "as": "sku"
            }
        },
        {
            "$addFields": {
                "spu": {
                    "$arrayElemAt": ["$spu", 0]
                },
                "sku": {
                    "$arrayElemAt": ["$sku", 0]
                }
            }
        },
        {
            "$addFields": {
                "articleNumber": "$spu.articleNumber",
                "size": {
                    "$function": {
                        "body": "function (jsonString) { return Object.values(JSON.parse(jsonString)).join(' '); }",
                        "args": ["$sku.properties"],
                        "lang": "js"
                    }
                }
            }
        },
        {
            "$project": {
                "articleNumber": 1,
                "size": 1,
                "stock_num": 1,
                "warehouseName": 1,
                "merchantCode": 1,
                "merchantName": 1,
                "appointNo": 1,
                "_id": 0
            }
        }
    ]

    if db == '集物':
        res = jw.stocks.aggregate(pipeline=pipeline)
        for i in res:
            li.append(
                list(i.values()))
    elif db == '淘物':
        res = taowu.stocks.aggregate(pipeline=pipeline)
        for i in res:
            li.append(
                list(i.values()))

    write_xlsx(['商家编号', '商家名称', '所在园区', '预约单号', '在仓库存数量', '货号', '尺码'], li,
               f'{db}闪电仓{datetime.now().strftime("%m%d")}')


# flash_stock_export('淘物')
